set more off

foreach i of numlist 1/86 {
import excel using "$data/women_age_census_1979_edit.xlsx", clear firstrow sheet("Sheet`i'") cellrange(A5:K125)
drop if Age==" 0-4" | Age==" 5-9" | Age==" 10-14" | Age==" 15-19" | Age==" 20-24" | Age==" 25-29" | Age==" 30-34" | Age==" 35-39" | Age==" 40-44" | Age==" 45-49" | Age==" 50-54" | Age==" 55-59" | Age==" 60-64" | Age==" 65-69" | Age==" 70-74" | Age==" 75-79" | Age==" 80-84" | Age==" 85-89" | Age==" 90-94" | Age==" 95-99" 
destring Age, gen(age_num)
destring Women, gen(women_num)
destring Women_urb, gen(women_urb_num)
destring Women_rur, gen(women_rur_num)
destring Both_sex, gen(numbirth)
destring Both_sex_urb, gen(numbirth_urb)
destring Both_sex_rur, gen(numbirth_rur)
drop Age Both_sex Men Women Both_sex_urb Both_sex_rur /*wom_sub wom_urb_sub wom_rur_sub*/ Men_rur Women_rur Men_urb Women_urb
sort Birthplace_code
save "$datatemp/women_age_census_1979_`i'_urb.dta", replace
}

* Moscow and St. Petersburg -- they have zero urban/rural population
forvalues i=87/88 { 
import excel using "$data/women_age_census_1979_edit.xlsx", clear firstrow sheet("Sheet`i'") cellrange(A5:K125)
drop if Age==" 0-4" | Age==" 5-9" | Age==" 10-14" | Age==" 15-19" | Age==" 20-24" | Age==" 25-29" | Age==" 30-34" | Age==" 35-39" | Age==" 40-44" | Age==" 45-49" | Age==" 50-54" | Age==" 55-59" | Age==" 60-64" | Age==" 65-69" | Age==" 70-74" | Age==" 75-79" | Age==" 80-84" | Age==" 85-89" | Age==" 90-94" | Age==" 95-99" 
destring Age, gen(age_num)
destring Women, gen(women_num)
destring Women_urb, gen(women_urb_num)
destring Both_sex, gen(numbirth)
destring Both_sex_urb, gen(numbirth_urb)
gen women_rur_num= 0
gen numbirth_rur = 0
drop Age Both_sex Men Women Both_sex_urb Both_sex_rur /*wom_sub wom_urb_sub wom_rur_sub*/ Men_rur Women_rur Men_urb Women_urb
sort Birthplace_code
save "$datatemp/women_age_census_1979_`i'_urb.dta", replace
}

use "$datatemp/women_age_census_1979_1_urb.dta", clear
forvalues i = 2/88{
append using "$datatemp/women_age_census_1979_`i'_urb.dta"
}
save "$datatemp/women_age_census_1979_all_urb.dta", replace

*** Create the number of women who are 15 to 44 by oblast in 1979. 
use "$datatemp/women_age_census_1979_all_urb.dta", clear
keep if age_num>=15 & age_num<=44
collapse (sum) women_urb_num women_rur_num, by(Birthplace_code)
rename women_urb_num women_urb_79
rename women_rur_num women_rur_79
sort Birthplace_code
save "$datatemp/women1544_79cens.dta", replace
